﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
namespace PrinteMaster
{
    class DataBase
    {
        /// <summary>
        /// 查询是否有该id的记录，如果有，username会返回该用户名，并且函数返回Error.ok ,如果没有返回Error.id
        /// </summary>
        /// <param name="id">[in]账号*qq号</param>
        /// <param name="userName">[out]用户名</param>
        /// <returns></returns>
        public Error isUserId(string id, out string userName)
        {
            userName = null;
            ////////////////
            //// sql语句
            var str = string.Format(
                "select 姓名 from 会员简 where 账号='{0}'"
                , id);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    userName = row[0][0].ToString();
                    return Error.ok;
                }
                else
                    return Error.id;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        private static DataBase m_DataBase = new DataBase();
        public static DataBase getInstance() { return m_DataBase; }
        public enum Error
        {

            手机号不存在,
            /// <summary>
            /// 网络错误，检查网络状态.
            /// </summary>
            network,
            /// <summary>
            /// 账号或密码错误
            /// </summary>
            id,
            /// <summary>
            /// 未知错误
            /// </summary>
            nothing,
            /// <summary>
            /// 一切顺利
            /// </summary>
            ok,
            /// <summary>
            /// 数据库报错
            /// </summary>
            databaseError,
            /// <summary>
            /// 打印机名字错误
            /// </summary>
            printerName,
            /// <summary>
            /// 转让目标不存在
            /// </summary>
            traget,
            /// <summary>
            /// 用户所选的打印机无效
            /// </summary>
            printerInvalid,
            /// <summary>
            /// 账号密码相同，为首次登陆，需要修改密码才能使用
            /// </summary>
            firstLogin,
            /// <summary>
            /// 账号等待管理员确认中.
            /// </summary>
            waitOk,
            已有该账号
        }
        /// <summary>
        /// 增加用户的余额
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="money"></param>
        /// <returns></returns>
        public Error addUserMoney(string userId, double money)
        {
            var str2 = string.Format(
                "update 会员简 set 余额=余额+{0} where 账号 = '{1}'"
                , money, userId);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str2, null);
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

            return Error.ok;
        }
        public Error isUserId(string id, out string userNumber, out string userName, out string phoneNumber)
        {
            userName = null;
            userNumber = null;
            phoneNumber = null;
            ////////////////
            //// sql语句
            var str = string.Format(
                "select 姓名,编号,手机号 from 会员简 where 账号='{0}'"
                , id);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    userNumber = row[0][1].ToString();
                    userName = row[0][0].ToString();
                    phoneNumber = row[0][2].ToString();
                    return Error.ok;
                }
                else
                    return Error.id;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public class PayInfo
        {
            public double money = 0;
            public string userNumber = "";
            public string userId = "";
            public string userName = "";
            public DateTime time = new DateTime();
            public string managerId = "";
            public string managerName = "";
            public string userPhoneNumber = "";
        }
        public Error addPayInfo(PayInfo info)
        {
            var str = string.Format(                                                                    //充值金额,会员编号,会员账户,会员姓名,时间,操作员ID,操作员姓名,手机号
                "insert into 充值记录表 (充值金额,会员编号,会员账户,会员姓名,时间,操作员ID,操作员姓名,手机号) value ({0},{1},'{2}','{3}','{4}',{5},'{6}','{7}')"
                , info.money, info.userNumber, info.userId, info.userName,
                info.time.ToString("yyyy-MM-dd HH:mm:ss"), info.managerId, info.managerName, info.userPhoneNumber);
            try
            {
                MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

        }
        public class UserInfo
        {
            public string id;
            public string pw;
            public string name;
            public double money;
            public string phoneNumber;
            public string shortPhoneNumber;
        }
        public Error addUser(UserInfo info)
        {
            //先验证有效性，手机号和账号要求唯一
            var str2 = string.Format(
                "select * from 会员简 where 账号='{0}' or 手机号='{1}' ", info.id, info.phoneNumber);
            try
            {
                var req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str2, null);
                if (req.Tables[0].Rows.Count != 0)
                {
                    return Error.已有该账号;
                }
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

            var str = string.Format(                                                                    //账号,密码,姓名,余额,//手机号,短手机号,总计打印任务数,总页数,//总花费,创建时间,验证状态
                "insert into 会员简 (账号,密码,姓名,余额,手机号,短手机号,总计打印任务数,总页数,总花费,创建时间,验证状态) value ('{0}','{1}','{2}','{3}','{4}','{5}',{6},{7},{8},'{9}','{10}')"
                , info.id, info.pw, info.name, info.money, info.phoneNumber, info.shortPhoneNumber, 0, 0, 0,
                DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "未验证");
            try
            {
                MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        /// <summary>
        /// 通用数据查询接口
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="where">条件【加where 关键字的】</param>
        /// <param name="row"></param>
        /// <returns></returns>
        public Error selectTable(string tableName, string where, out DataSet req)
        {
            req = null;
            ////////////////
            //// sql语句
            var str = string.Format(
                "select * from {0} {1}", tableName, where);
            try
            {
                req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    return Error.ok;
                }
                else
                {
                    return Error.nothing;
                }
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

        }

        public Error isUserOnline(out DataSet req)
        {
            req = null;
            ////////////////
            //// sql语句
            var str = string.Format(
                "select * from 客户端连接");
            try
            {
                req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    for (int i = 0; i < row.Count; i++)
                    {
                        if (row[i][5].ToString().Equals("离线"))
                        {
                            str = string.Format("会员{0}在{1}异常离线，请检查!",
                            row[i][3].ToString(), row[i][2].ToString());
                            //MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                        }
                    }
                    str = string.Format("update 客户端连接 set 状态='离线'");
                    MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                    return Error.ok;
                }
                else
                    return Error.id;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public class SyetemSetting
        {
            public static DateTime time = new DateTime();
            public static int m_SingleTimeSec = 1;
            public static int m_WaitTimeSec = 5;
            public enum AutoPay
            {
                开启, 未开启
            }
            public static AutoPay m_autoPayState = AutoPay.未开启;
            public static int Min = 10;
            public static double MoneySub = 2;
            public static string code = "";
            public static double codeMoney = 0.0;
        }
        public Error getSystemSetting()
        {
            var str = string.Format(
                "select 自动关机时间,单个等待时间,固定等待时间,自助充值开启状态,每几分钟,扣几毛钱,优惠码,优惠金额 from 系统设置");
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {

                    SyetemSetting.time = DateTime.Parse(row[0][0].ToString());
                    SyetemSetting.m_SingleTimeSec = Convert.ToInt32(row[0][1].ToString());
                    SyetemSetting.m_WaitTimeSec = Convert.ToInt32(row[0][2].ToString());
                    if (row[0][3].ToString() == "开启")
                        SyetemSetting.m_autoPayState = SyetemSetting.AutoPay.开启;
                    else
                        SyetemSetting.m_autoPayState = SyetemSetting.AutoPay.未开启;
                    SyetemSetting.MoneySub = Convert.ToDouble(row[0][5].ToString());
                    SyetemSetting.Min = Convert.ToInt32(row[0][4].ToString());
                    SyetemSetting.code = row[0][6].ToString();
                    SyetemSetting.codeMoney = Convert.ToDouble(row[0][7].ToString());
                    return Error.ok;
                }
                else
                    return Error.databaseError;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public Error setSystemSetting(DateTime autoExitTime, uint waitTime, SyetemSetting.AutoPay autoPay, uint min, uint subMoney, string code, string codeMoney)
        {
            var str = string.Format(
                "update 系统设置 set 自动关机时间='{0}',单个等待时间=1,固定等待时间='{1}',自助充值开启状态 = '{2}',每几分钟 = {3},扣几毛钱 ={4},优惠码={5},优惠金额={6}",
                autoExitTime.ToString("HH:mm:ss"), waitTime, autoPay.ToString(), min.ToString(), subMoney.ToString(), code, codeMoney);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public Error updateUserMoney(string phoneNumber, string value)
        {
            var str = string.Format(
                "select * from 会员简 where 手机号='{0}'",phoneNumber
                );

            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count == 0)
                {
                    //如果返回数量为0
                    //直接返回并显示错误
                    //MessageBox.Show("手机号为"+phoneNumber+"的用户不存在!");
                    return Error.手机号不存在;
                }

            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        
            str = string.Format(
                "update 会员简 set 余额={0} where 手机号='{1}'", value,phoneNumber
                );

            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public Error getWaitOkUsers(out DataSet req)
        {
            req = null;
            ////////////////
            //// sql语句
            var str = string.Format(
                "select * from 会员简 where 验证状态='未验证'");
            try
            {
                req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    return Error.ok;
                }
                else
                    return Error.nothing;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

        }

        public Error setWaitOkUser(string userId)
        {

            var str = string.Format(
                "update 会员简 set 验证状态='已验证' where 编号 = {0}",
                userId);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }

        public Error isManager(string userId)
        {
            ////////////////
            //// sql语句
            var str = string.Format(
                "select * from 员工账号 where 账号='{0}'", userId);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count == 0)
                {
                    return Error.ok;
                }
                else
                    return Error.已有该账号;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

        }

        public Error regiserManager(string id, string pw, string name, string phoneNumber, string shortPhoneNumber)
        {
            //判断有效性
            Error er = isManager(id);
            if (er == Error.已有该账号)
                return er;

            //insert into 员工账号 (账号,密码,员工姓名,员工手机号) value ('7','7','asdsad','15912345678')
            var str = string.Format(
                "insert into 员工账号 (账号,密码,员工姓名,员工手机号,手机短号,创建时间) value ('{0}','{1}','{2}','{3}','{4}','{5}')",
                id, pw, name, phoneNumber, shortPhoneNumber, DateTime.Now.ToString("yyy-MM-dd HH:mm:ss"));
            try
            {

                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public class PrinterState
        {
            public enum color颜色 { b黑白, c彩色, j兼容 }
            /// <summary>
            /// 指示打印机的黑白或彩色 使用枚举变量DataBase.PrinterState.color颜色
            /// </summary>
            public color颜色 color = color颜色.b黑白;
            /// <summary>
            /// 指示打印机的名字
            /// </summary>
            public string printerName = "";
            public enum state状态 { k空闲, z作业, d等待, z暂停 }
            /// <summary>
            /// 指示打印机的状态，使用枚举变量DataBase.PrinterState.state状态
            /// </summary>
            public state状态 state = state状态.k空闲;
            private static PrinterState m_PrinterState = new PrinterState();
            /// <summary>
            /// 通过该函数获取唯一实例
            /// </summary>
            /// <returns></returns>
            public static PrinterState getInstance() { return m_PrinterState; }
            /// <summary>
            /// 彩色每页钱数
            /// </summary>
            public double rgbMoney = 1.0;
            /// <summary>
            /// 黑白每页钱数
            /// </summary>
            public double blackMoney = 0.1;
            /// <summary>
            /// 彩色A3每页
            /// </summary>
            public double rgbA3Money = 0.8;
            /// <summary>
            /// 黑白A3每页
            /// </summary>
            public double blackA3Money = 3.0;
            public string useUserId = "";
        }
        public Error getPrinterState(string name, out PrinterState printerState, out DataSet req)
        {
            printerState = PrinterState.getInstance();
            ////////////////
            //// sql语句
            req = null;
            var str = string.Format(
            "select 打印机状态,黑白每页,彩色每页,打印机颜色,打印机名,彩色A3每页,黑白A3每页,占用者ID from 打印机状态表 where 打印机名 = '{0}'", name);
            try
            {
                req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    switch (row[0][0].ToString())
                    {
                        case "空闲":
                            printerState.state = PrinterState.state状态.k空闲;
                            break;
                        case "作业":
                            printerState.state = PrinterState.state状态.z作业;
                            break;
                        case "等待":
                            printerState.state = PrinterState.state状态.d等待;
                            break;
                        case "暂停":
                            printerState.state = PrinterState.state状态.z暂停;
                            break;
                    }
                    printerState.blackMoney = Convert.ToDouble(row[0][1].ToString());
                    printerState.rgbMoney = Convert.ToDouble(row[0][2].ToString());
                    switch (row[0][3].ToString())
                    {
                        case "黑白":
                            printerState.color = PrinterState.color颜色.b黑白;
                            break;
                        case "彩色":
                            printerState.color = PrinterState.color颜色.c彩色;
                            break;
                        case "兼容":
                            printerState.color = PrinterState.color颜色.j兼容;
                            break;
                    }
                    printerState.printerName = row[0][4].ToString();
                    printerState.rgbA3Money = Convert.ToDouble(row[0][5].ToString());
                    printerState.blackA3Money = Convert.ToDouble(row[0][6].ToString());
                    printerState.useUserId = row[0][7].ToString();
                    return Error.ok;
                }
                else
                {
                    // 如果返回的数据长度为0，则意味着打印机名错误
                    return Error.printerName;
                }
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public Error deletePrinter(string name)
        {
            var str = string.Format(
                "delete from 打印机状态表 where 打印机名 = '{0}'", name);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

        }
        public Error addPrinter(PrinterState state)
        {
            string color = "";
            switch (state.color)
            {
                case PrinterState.color颜色.b黑白:
                    color = "黑白";
                    break;
                case PrinterState.color颜色.c彩色:
                    color = "彩色";
                    break;
            }
            var str = string.Format(
                "insert into 打印机状态表 (打印机名,打印机状态,黑白每页,彩色每页,打印机颜色,彩色A3每页,黑白A3每页) value ('{0}','空闲',{1},{2},'{3}',{4},{5})",
                state.printerName, state.blackMoney, state.rgbMoney,
                color, state.rgbA3Money, state.blackA3Money);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        public Error updatePrinter(PrinterState state)
        {
            string color = "";
            switch (state.color)
            {
                case PrinterState.color颜色.b黑白:
                    color = "黑白";
                    break;
                case PrinterState.color颜色.c彩色:
                    color = "彩色";
                    break;
            }
            var str = string.Format(
                "update 打印机状态表 set 黑白每页={0},彩色每页={1},打印机颜色='{2}',彩色A3每页={3},黑白A3每页={4} where 打印机名='{5}'",
                state.blackMoney, state.rgbMoney,
                color, state.rgbA3Money, state.blackA3Money, state.printerName);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        /// <summary>
        /// 返回用户剩余的余额
        /// [return Error] .ok  |  .dataError  |  .id
        /// </summary>
        /// <param name="userId">账号</param>
        /// <param name="money">返回的余额</param>
        /// <returns></returns>
        public Error getUserMoney(string userId, out double money)
        {
            money = 0f;
            var str = string.Format(
                "select 余额 from 会员简 where 账号= '{0}'"
                , userId);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    money = Convert.ToDouble(row[0][0].ToString());
                    return Error.ok;
                }
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
            return Error.id;
        }
        /// <summary>
        /// 减少用户的余额
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="money"></param>
        /// <returns></returns>
        public Error subUserMoney(string userId, double money)
        {
            var str2 = string.Format(
                "update 会员简 set 余额=余额-{0} where 账号 = '{1}'"
                , money, userId);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str2, null);
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }

            return Error.ok;
        }
        public class ManagerExitState
        {
            public string name = "";
            public string id = "";
            public DateTime stratTime = DateTime.Now;
            public DateTime endTime = DateTime.Now;
            public double money = 0;
        }
        public Error managerExit(ManagerExitState state)
        {
            var str2 = string.Format(
                "insert into 员工交接班 (员工账号,员工姓名,应交金额,上班时间,下班时间) value ('{0}','{1}',{2},'{3}','{4}')",
                state.id, state.name, state.money,
                state.stratTime.ToString("yyyy-MM-dd HH:mm:ss"),
                state.endTime.ToString("yyyy-MM-dd HH:mm:ss"));

            try
            {
                MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str2, null);
                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
        }
        /// <summary>
        /// 查询并获取一段时间内某个员工所充值的所有金额之和。
        /// 如果没有该员工的记录，返回一个error.nothing，
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="managerNumber"></param>
        /// <param name="money"></param>
        /// <returns></returns>
        public Error getMoneySumForTime(DateTime startTime, DateTime endTime, string managerNumber, out double money)
        {
            money = 0;
            string str = null;
            try
            {
                str = string.Format(
                    "select 充值金额 from 充值记录表 where 操作员ID = '{0}' and 时间 between '{1}' and '{2}'",
                    managerNumber, startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            catch (Exception ex) { var s = ex.Message; }
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str, null);
                DataRowCollection row = req.Tables[0].Rows;
                if (row.Count != 0)
                {
                    for (int i = 0; i < row.Count; i++)
                    {
                        money += Convert.ToDouble(row[i][0].ToString());
                    }
                    return Error.ok;
                }
                else
                {
                    return Error.nothing;
                }
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }
            //return Error.id;
        }
        public Error updateManagerExitState(string number)
        {
            var str2 = string.Format(
                "update 员工交接班 set 是否收款='已收款' where 编号 = {0}"
                , number);
            try
            {
                DataSet req = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, str2, null);

                return Error.ok;
            }
            catch (Exception ex) { Console.Write(ex.Message); return Error.databaseError; }


        }
        public Error getMoneyForPage(string name, out double A4, out double A3)
        {
            var printerState = new PrinterState();
            var req = new DataSet();
            var e = getPrinterState(name, out printerState, out req);
            A4 = printerState.blackMoney;
            A3 = printerState.blackA3Money;
            return e;
        }

    }
}
